package com.ewancle.utils;

import com.alibaba.fastjson2.JSONObject;
import com.ewancle.model.KnowlCat;
import com.ewancle.model.Subject;
import com.ewancle.model.Topic;
import com.ewancle.model.TopicSubject;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

public class PostgreSqlJdbc {

    private static final String URL = "jdbc:postgresql://47.116.210.68:3269/ahuyk_db";
    private static final String USER = "ahuyk";
    private static final String PASSWORD = "ahuyk@Ga1@#$%^";

    private static final HikariConfig config = new HikariConfig();
    private static final HikariDataSource ds;

    static {

       /* jdbcUrl=jdbc:mysql://localhost:3306/simpsons
        username=test
        password=test
        dataSource.cachePrepStmts=true
        dataSource.prepStmtCacheSize=250
        dataSource.prepStmtCacheSqlLimit=2048
        dataSource.useServerPrepStmts=true
        dataSource.useLocalSessionState=true
        dataSource.rewriteBatchedStatements=true
        dataSource.cacheResultSetMetadata=true
        dataSource.cacheServerConfiguration=true
        dataSource.elideSetAutoCommits=true
        dataSource.maintainTimeStats=false*/

        config.setJdbcUrl(URL);
        config.setUsername(USER);
        config.setPassword(PASSWORD);
        config.addDataSourceProperty( "cachePrepStmts" , "true" );
        config.addDataSourceProperty( "prepStmtCacheSize" , "250" );
        config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );

        ds = new HikariDataSource( config );

    }

    // 关闭默认构造函数
    private PostgreSqlJdbc() {}

    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    public static Connection getConnectionReadOnly() throws SQLException {
        Connection c = ds.getConnection();
        c.setReadOnly(true);
        return c;
    }
    public static Connection getConnectionManualCommit() throws SQLException {
        Connection c = ds.getConnection();
        c.setAutoCommit(false);
        return c;
    }

    /**
     * 封装统一的参数设置
     * @param con
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static PreparedStatement setPreparedStatementParams(Connection con, final String sql,
                                                               Object[] params) throws SQLException {
        PreparedStatement ps = con.prepareStatement(sql);
        if(Objects.nonNull(params) && params.length > 0){
            int size = params.length;
            for(int i = 1; i <= size; i++){
                Object item = params[i-1];
                if(item instanceof String){
                    ps.setString(i, ((String) item).trim());
                }else if (item instanceof Integer) {
                    ps.setInt(i, (Integer) item);
                }else if (item instanceof Long) {
                    ps.setLong(i, (Long) item);
                }else if (item instanceof Double) {
                    ps.setDouble(i, (Double) item);
                }else if (item instanceof Float) {
                    ps.setFloat(i, (Float) item);
                }else if (item instanceof Array) {
                    ps.setArray(i, (Array) item);
                }else if (item instanceof LocalDateTime dateTime) {
                    // https://www.baeldung.com/java-date-to-localdate-and-localdatetime
                    Date date = Date
                            .from(dateTime.atZone(ZoneId.systemDefault())
                                    .toInstant());
                    //https://blog.csdn.net/engerla/article/details/81298645
                    // getTime() 方法返回的是自1970年1月1日00:00:00以来的毫秒数，表示根据协调世界时（UTC）指定日期的时间所对应的数字值
                    //ps.setDate(i, new java.sql.Date(date.getTime()));

                    //获取秒数
                    //Long second = LocalDateTime.now().toEpochSecond(ZoneOffset.of("+8"));
                    //获取毫秒数
                    //Long milliSecond = LocalDateTime.now().toInstant(ZoneOffset.of("+8")).toEpochMilli();
                    ZoneOffset zoneOffset = ZoneId.systemDefault().getRules().getOffset(dateTime);
                    ps.setDate(i, new java.sql.Date(dateTime.toInstant(zoneOffset).toEpochMilli()));

                }
                // 设置null
                //ps.setNull(6, java.sql.Types.VARCHAR);

            }
        }
        return ps;
    }

    /*
    3) 通过Connection对象创建Statement对象，Connection创建Statement对象的方法有如下3个
    3.1) createStatement(String sql):创建基本的Statement对象
    3.2) prepareStatement(String sql): 根据传入的SQL语句创建预编译的Statement对象
    3.3) prepareCall(String sql): 根据传入的SQL语句创建CallableStatement对象
    4) 使用Statement执行SQL语句
            所有的Statement都有如下3个方法来执行SQL语句
    4.1) execute(): 可以执行任何SQL语句，但比较麻烦
    4.2) executeUpdate(): 主要用于执行DML和DDL语句。执行DML语句返回受SQL影响的行数，执行DDL语句返回0
    4.3) executeQuery(): 只能执行查询语句，执行后返回代表查询结果的ResultSet对象*/

    public static int batchInsertKnowlCat(List<KnowlCat> params, final String sql, int batchSize) throws SQLException {
        int insertRow = 0;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = PostgreSqlJdbc.getConnectionManualCommit();
            pst = con.prepareStatement(sql); // 预编译

            int size = params.size();
            for(int i = 0; i < size; i++){
                KnowlCat knowlCat = params.get(i);

                pst.setLong(1, knowlCat.getId());
                pst.setString(2, knowlCat.getName());
                pst.setLong(3,knowlCat.getParentId());

                String href = knowlCat.getHref();
                if (Objects.nonNull(href) && ! href.trim().isEmpty()) {
                    pst.setString(4, href);
                }else {
                    pst.setNull(4, java.sql.Types.VARCHAR);
                }

                Integer mainCid = knowlCat.getMainCid();
                if (Objects.nonNull(mainCid)) {
                    pst.setInt(5, mainCid);
                }else {
                    pst.setNull(5, Types.INTEGER);
                }

                String desc = knowlCat.getDesc();
                if (Objects.nonNull(desc) && ! desc.trim().isEmpty()) {
                    pst.setString(6, desc);
                }else {
                    pst.setNull(6, java.sql.Types.VARCHAR);
                }

                insertRow += pst.executeUpdate();
                if((i/batchSize > 0 && i%batchSize == 0) || i == size-1){
                    con.commit();
                }
            }

            return insertRow;
        }catch (SQLException e){
            con.rollback();
            throw e;
        }finally {
            if(con != null){
                con.commit();
            }
            if(pst != null){
                pst.close();
                con.close();
            }
        }
    }

    public static int batchInsertTopic(List<Topic> params, final String sql, int batchSize) throws SQLException {
        int insertRow = 0;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = PostgreSqlJdbc.getConnectionManualCommit();
            pst = con.prepareStatement(sql); // 预编译

            int size = params.size();
            for(int i = 0; i < size; i++){
                Topic topic = params.get(i);

                pst.setLong(1, topic.getId());
                pst.setString(2, topic.getName());
                pst.setLong(3,topic.getKnowlCatId());

                String clickUrl = topic.getClickUrl();
                if (Objects.nonNull(clickUrl) && ! clickUrl.trim().isEmpty()) {
                    pst.setString(4, clickUrl);
                }else {
                    pst.setNull(4, java.sql.Types.VARCHAR);
                }

                Integer status = topic.getStatus();
                if (Objects.nonNull(status) && status > 0) {
                    pst.setInt(5, status);
                }else {
                    pst.setNull(5, Types.INTEGER);
                }

                insertRow += pst.executeUpdate();
                if((i/batchSize > 0 && i%batchSize == 0) || i == size-1){
                    con.commit();
                }
            }
            return insertRow;
        }catch (SQLException e){
            con.rollback();
            throw e;
        }finally {
            if(con != null){
                con.commit();
            }
            if(pst != null){
                pst.close();
                con.close();
            }
        }
    }

    public static int batchInsertSubject(List<Subject> params, final String sql, int batchSize) throws SQLException {
        int insertRow = 0;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = PostgreSqlJdbc.getConnectionManualCommit();
            pst = con.prepareStatement(sql); // 预编译

            int size = params.size();
            for(int i = 0; i < size; i++){
                Subject subject = params.get(i);

                pst.setLong(1, subject.getId());
                pst.setString(2, subject.getName());
                pst.setLong(3, subject.getParentId());

                String href = subject.getHref();
                if (Objects.nonNull(href) && ! href.trim().isEmpty()) {
                    pst.setString(4, href);
                }else {
                    pst.setNull(4, java.sql.Types.VARCHAR);
                }

                String onclick = subject.getOnclick();
                if (Objects.nonNull(onclick) && ! onclick.trim().isEmpty()) {
                    pst.setString(5, onclick);
                }else {
                    pst.setNull(5, java.sql.Types.VARCHAR);
                }

                Long partId = subject.getPartId();
                if (Objects.nonNull(partId) && partId > 0) {
                    pst.setLong(6, partId);
                }else {
                    pst.setNull(6, Types.INTEGER);
                }

                Integer status = subject.getStatus();
                if (Objects.nonNull(status) && status > 0) {
                    pst.setInt(7, status);
                }else {
                    pst.setNull(7, Types.INTEGER);
                }

                insertRow += pst.executeUpdate();
                if((i/batchSize > 0 && i%batchSize == 0) || i == size-1){
                    con.commit();
                }
            }
            return insertRow;
        }catch (SQLException e){
            con.rollback();
            throw e;
        }finally {
            if(con != null){
                con.commit();
            }
            if(pst != null){
                pst.close();
                con.close();
            }
        }
    }
    public static int batchInsertTopicSubject(List<TopicSubject> params, final String sql, int batchSize) throws SQLException {
        int insertRow = 0;
        Connection con = null;
        PreparedStatement pst = null;
        try {
            con = PostgreSqlJdbc.getConnectionManualCommit();
            pst = con.prepareStatement(sql); // 预编译

            int size = params.size();
            for(int i = 0; i < size; i++){
                TopicSubject topicSubject = params.get(i);
                pst.setLong(1, topicSubject.getTopicId());
                pst.setLong(2,topicSubject.getSubjectId());
                insertRow += pst.executeUpdate();
                if((i/batchSize > 0 && i%batchSize == 0) || i == size-1){
                    con.commit();
                }
            }
            return insertRow;
        }catch (SQLException e){
            con.rollback();
            throw e;
        }finally {
            if(con != null){
                con.commit();
            }
            if(pst != null){
                pst.close();
                con.close();
            }
        }
    }


    /**
     * 查询分类信息
     * @param params
     * @param sql
     * @return
     * @throws SQLException
     */
    public static List<KnowlCat> queryKnowlCat(List<Object> params, final String sql) throws SQLException {
        //String SQL_QUERY = "select * from emp";
        List<KnowlCat> knowlCatList = null;
        try (Connection con = PostgreSqlJdbc.getConnectionReadOnly();
             PreparedStatement pst = setPreparedStatementParams(con, sql, params.toArray());
             ResultSet rs = pst.executeQuery();) {

            knowlCatList = new ArrayList<>();
            KnowlCat knowlCat;
            //int columnCount = rs.getMetaData().getColumnCount();
            while ( rs.next() ) {
                knowlCat = new KnowlCat();

                knowlCat.setId(rs.getLong("id")); // index 从1开始，一般使用字段名
                knowlCat.setName(rs.getString("name"));
                knowlCat.setParentId(rs.getLong("parentId"));

                rs.getString("href");
                // wasNULL()方法会判断最后一个接收到的最后一个值是否为空
                boolean bool = rs.wasNull();
                if(! bool){
                    knowlCat.setHref(rs.getString("href"));
                }

                rs.getInt("mainCid");
                if(!rs.wasNull()){
                    knowlCat.setMainCid(rs.getInt("mainCid"));
                }

                rs.getString("desc");
                if(!rs.wasNull()) knowlCat.setDesc(rs.getString("desc"));

                knowlCatList.add(knowlCat);
            }
        }
        return knowlCatList;
    }

    public static List<Subject> queryPaquSubject(List<Object> params, final String sql) throws SQLException {
        //String SQL_QUERY = "select * from emp";
        List<Subject> subjectList = null;
        try (Connection con = PostgreSqlJdbc.getConnectionReadOnly();
             PreparedStatement pst = setPreparedStatementParams(con, sql, params.toArray());
             ResultSet rs = pst.executeQuery();) {

            subjectList = new ArrayList<>();
            Subject subject;
            //int columnCount = rs.getMetaData().getColumnCount();
            while ( rs.next() ) {
                subject = new Subject();

                subject.setId(rs.getLong("id")); // index 从1开始，一般使用字段名
                subject.setName(rs.getString("name"));
                subject.setParentId(rs.getLong("parentId"));

                rs.getString("href");
                // wasNULL()方法会判断最后一个接收到的最后一个值是否为空
                boolean bool = rs.wasNull();
                if(! bool){
                    subject.setHref(rs.getString("href"));
                }

                rs.getString("onclick");
                // wasNULL()方法会判断最后一个接收到的最后一个值是否为空
                boolean bool1 = rs.wasNull();
                if(! bool1){
                    subject.setOnclick(rs.getString("onclick"));
                }

                /*rs.getInt("mainCid");
                if(!rs.wasNull()){
                    knowlCat.setMainCid(rs.getInt("mainCid"));
                }*/

                rs.getLong("partId");
                if(!rs.wasNull()) subject.setPartId(rs.getLong("partId"));

                subjectList.add(subject);
            }
        }
        return subjectList;
    }

    public static void insertUser(String name, JSONObject info) {

        // 测试插入 JSON 处理：org.json 库中的 JSONObject 类可用于创建和处理 JSON 数据。
        /*JSONObject jsonInfo = new JSONObject();
        jsonInfo.put("email", "john.doe@example.com");
        jsonInfo.put("age", 30);
        jsonInfo.put("active", true);

        insertUser("John Doe", jsonInfo);*/

        // SQL 查询：INSERT INTO users (name, info) VALUES (?, ?::jsonb) 查询会在 users 表中插入一条记录。?::jsonb
        // 语法是 PostgreSQL 特有的语法，用于类型转换。双冒号操作符 :: 是 PostgreSQL 中 CAST 关键字的同义词，表示类型转换操作。
        // 通过使用 ?::jsonb，我们指定 PostgreSQL 将第二个参数（一个 JSON 字符串）转换为 jsonb 数据类型，然后再将其插入 info 列。
        // 这样就可以在 PostgreSQL 中正确处理和存储 JSON 数据。
        String sql = "INSERT INTO users (name, info) VALUES (?, ?::jsonb)";

        try {
            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // PreparedStatement：PreparedStatement 设置参数并执行 SQL 查询。pstmt.setString(1, name) 设置 name 参数，
            // pstmt.setString(2, info.toString()) 设置 info 参数，即 JSON 数据。
            PreparedStatement pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, name);
            // JSON 对象序列化为字符串格式
            pstmt.setString(2, info.toString());
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        System.out.println("Data inserted successfully.");
    }

    /**
     * JDBC连接到PostgreSQL数据库
     */
    public static void testConnection() {
        Connection c = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection("jdbc:postgresql://47.116.210.68:3269/ahuyk_db",
                            "ahuyk", "ahuyk@Ga1@#$%^");
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName()+": "+e.getMessage());
            System.exit(0);
        }
        System.out.println("Opened database successfully");
    }

    // 创建表
    public static void createTab() {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection("jdbc:postgresql://localhost:5432/db_person",
                            "postgres", "123456");
            System.out.println("连接数据库成功！");
            stmt = c.createStatement();
            String sql = "CREATE TABLE COMPANY02 " +
                    "(ID INT PRIMARY KEY     NOT NULL," +
                    " NAME           TEXT    NOT NULL, " +
                    " AGE            INT     NOT NULL, " +
                    " ADDRESS        CHAR(50), " +
                    " SALARY         REAL)";
            stmt.executeUpdate(sql);
            stmt.close();
            c.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName()+": "+e.getMessage());
            System.exit(0);
        }
        System.out.println("新表创建成功！");
    }

    // 插入数据
    public static void insert() {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/db_person", "postgres",
                    "123456");
            c.setAutoCommit(false);

            System.out.println("连接数据库成功！");
            stmt = c.createStatement();

            String sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
            stmt.executeUpdate(sql);

            sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
            stmt.executeUpdate(sql);

            sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
            stmt.executeUpdate(sql);

            sql = "INSERT INTO COMPANY02 (ID,NAME,AGE,ADDRESS,SALARY) "
                    + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
            stmt.executeUpdate(sql);

            stmt.close();
            c.commit();
            c.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("新增数据成功！");
    }

    // 查询数据
    public static void select() {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/db_person", "postgres",
                    "123456");
            c.setAutoCommit(false);

            System.out.println("连接数据库成功！");
            stmt = c.createStatement();

            ResultSet rs = stmt.executeQuery("select * from company02");
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println(id + "," + name + "," + age + "," + address.trim() + "," + salary);
            }

            rs.close();
            stmt.close();

            c.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("查询数据成功！");
    }

    // 更新数据
    public static void update() {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/db_person", "postgres",
                    "123456");
            c.setAutoCommit(false);

            System.out.println("连接数据库成功！");
            stmt = c.createStatement();

            String sql = "Delete from COMPANY02 where ID=4 ";
            stmt.executeUpdate(sql);
            c.commit();

            ResultSet rs = stmt.executeQuery("select * from company02 order by id");
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println(id + "," + name + "," + age + "," + address.trim() + "," + salary);
            }

            rs.close();
            stmt.close();

            c.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("更新数据成功！");
    }

    // 删除
    public static void delete() {
        Connection c = null;
        Statement stmt = null;
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager.getConnection(
                    "jdbc:postgresql://localhost:5432/db_person", "postgres",
                    "123456");
            c.setAutoCommit(false);

            System.out.println("连接数据库成功！");
            stmt = c.createStatement();

            String sql = "UPDATE COMPANY02 set SALARY = 250 where ID=1 ";
            stmt.executeUpdate(sql);
            c.commit();

            ResultSet rs = stmt.executeQuery("select * from company02 order by id");
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");
                String address = rs.getString("address");
                float salary = rs.getFloat("salary");
                System.out.println(id + "," + name + "," + age + "," + address.trim() + "," + salary);
            }

            rs.close();
            stmt.close();

            c.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("删除数据成功！");
    }


}
